﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;
namespace Dblayer
{
    public class DbShowRoom
    {
        SqlConnection con;

        public DbShowRoom()
        {
            con = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dm76_4;Persist Security Info=True;User ID=DM76_4;Password=MaaGodt");
        }

        public int getShowRoomNumber()
        {
            int maxValue = 0;
            try
            {
                if (con == null)
                {
                    con.Open();
                }
                string getMax = @"SELECT MAX(ShowRoomNumber) FROM ShowRoom";
                SqlCommand myCommand = new SqlCommand(getMax, con);
                maxValue = (int)myCommand.ExecuteScalar();
                return maxValue;
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);

            }
            return maxValue;
        }   

        public void createShowRoom(ShowRoom sh)
        {
            try
            {
                con.Open();
                string insert = @"insert into ShowRoom(ShowRoomNumber, NumberOfSeats) values ('" + sh.showRoomNumber + "','"+sh.numberOfSeats+"')";
                SqlCommand myCommand = new SqlCommand(insert, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        public ShowRoom findShowRoom(string showRoomNumber)
        {
            ShowRoom sh = null;
            int SHN = 0;
            int NOS = 0;
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from ShowRoom where ShowRoomNumber = '" + showRoomNumber + "'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    SHN = (int)rdr["ShowRoomNumber"];
                    NOS = int.Parse(rdr["NumberOfSeats"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }
            }
            sh = new ShowRoom(SHN, NOS, "her skal beskrivelse være");
            return sh;
        }

        public void updateShowRoom(ShowRoom sh1)
        {
            try
            {
                con.Open();
                String updateQuery = @"update showroom set numberOfSeats = '"+sh1.numberOfSeats+"' where showRoom.ShowRoomNumber = '"+sh1.showRoomNumber+"'";
                SqlCommand myCommand = new SqlCommand(updateQuery, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            
        }
    }
}
    

